﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using XQ.Framework.model;
using XQ.Framework.core;

namespace lib
{

    /// <summary>
    /// 数据库类
    /// version:1.0
    /// 最后更新：2013.5.20  增加了sql参数传入
    /// 作者：王俊
    /// Email: hiwjcn@gmail.com
    /// ======================================================
    /// 注意事项：
    /// 1.所有执行都是默认不获取新的连接来节省资源，在使用reader后需要手动获取新连接【或者使用CloseReader()关闭现有连接】。
    /// 2.所有执行都是默认不自动关闭连接的，这时需要手动关闭连接。
    /// 4.如果有更改请不要改动【方法名】
    /// 5.使用sql参数相比拼sql可以防止SQL注入的危险！【用字典传入数据】
    /// ======================================================
    /// 用法：
    /// DataBase db = DataBaseFactory.getNewConnection(DataBaseType.mysql);
    /// db.ExecuteNonQuery(sql);
    /// db.ExecuteNonQuery(sql,sqlParams);
    /// ...do somethings
    /// db.Dispose();
    /// </summary>
    public class MySqlDB : IDataBase, IDisposable
    {
        #region 属性和构造
        private MySqlConnection con = null;
        private string conString = string.Empty;
        public int ConnectCount { get; set; }
        public int QueryCount { get; set; }
        //连接数据库触发的事件
        public event Action OnCreateNewConnectionHandler;
        public event Action OnConnectionClosed;
        /// <summary>
        /// 构造函数
        /// </summary>
        public MySqlDB()
        {
            this.SetDataBaseModel(new DataBaseModelBase());
        }
        /// <summary>
        /// c#的析构函数不适合用来关闭连接，因为gc操作不可预估
        /// </summary>
        ~MySqlDB()
        {
            //this.Release();
        }
        #endregion
        #region 设置数据库信息
        /// <summary>
        /// 设置数据库模型
        /// </summary>
        /// <param name="model"></param>
        public void SetDataBaseModel(DataBaseModelBase model)
        {
            /*
            string conStr = string.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};"
                , model.HostIP, model.Port, model.DbName, model.UserName, model.Pwd);
            conStr += (model.UseConnectionPool ?
                ";Pooling=true;Integrated Security=SSPI;Max Pool Size=20;Min Pool Size=5" : "Pooling=false");
            setConnectionString(conStr);*/
            //DbConnectionStringBuilder cs = new DbConnectionStringBuilder();
            MySqlConnectionStringBuilder cs = new MySqlConnectionStringBuilder();
            cs.Server = model.HostIP;
            cs.Port = (uint)model.Port;
            cs.Database = model.DbName;
            cs.UserID = model.UserName;
            cs.Password = model.Pwd;
            cs.CharacterSet = model.CharSet;
            cs.Pooling = model.UseConnectionPool;
            if (cs.Pooling)
            {
                cs.MaximumPoolSize = 20;
                cs.MinimumPoolSize = 5;
                //cs.IntegratedSecurity = true;
            }
            SetConnectionString(cs.GetConnectionString(true));
        }
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="constring"></param>
        public void SetConnectionString(string constring)
        {
            this.conString = constring;
        }
        #endregion
        #region 初始化连接并获取command对象
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IDbCommand InitCommand()
        {
            return InitCommand(CommandType.Text);
        }
        public IDbCommand InitCommand(CommandType cmdtype)
        {
            return InitCommand(cmdtype, 30);
        }
        public IDbCommand InitCommand(CommandType cmdtype, int timeOut)
        {
            try
            {
                if (con == null || con.State != ConnectionState.Open)
                {
                    //重新连接
                    this.Release();
                    con = new MySqlConnection();
                    con.ConnectionString = this.conString;
                    //添加事件
                    con.StateChange += ConnectionStateChange;
                    //执行事件
                    if (OnCreateNewConnectionHandler != null)
                    {
                        OnCreateNewConnectionHandler.Invoke();
                    }
                    con.Open();
                }
                DbCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandType = cmdtype;
                cmd.CommandTimeout = timeOut;
                ++this.ConnectCount;
                return cmd;
            }
            catch (Exception e)
            {
                this.Release();
                return null;
            }
        }
        /// <summary>
        /// 连接状态改变后触发的事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void ConnectionStateChange(object sender, StateChangeEventArgs e)
        {
            try
            {
                //Console.WriteLine(e.OriginalState.ToString() + "==" + e.CurrentState.ToString());
                //如果连接损坏就关闭
                if (e.CurrentState == ConnectionState.Broken)
                {
                    this.Release();
                }
            }
            catch { }
        }
        #endregion
        #region 执行非查询
        /// <summary>
        /// 执行非查询操作
        /// </summary>
        /// <param name="sql">sql语言</param>
        /// <returns>影响的条数</returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }
        public int ExecuteNonQuery(string sql, Dictionary<string, object> sqlParams)
        {
            ++this.QueryCount;
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return 0; }
                cmd.CommandText = sql;
                if (sqlParams != null)
                {
                    foreach (KeyValuePair<string, object> entry in sqlParams)
                    {
                        cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                    }
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }

        public int ExecuteNonQuery(string sql, IDataParameter[] sqlParams, int timeOut)
        {
            ++this.QueryCount;
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return 0; }
                cmd.CommandText = sql;
                if (sqlParams != null)
                {
                    for (int i = 0; i < sqlParams.Count(); ++i)
                    {
                        if (sqlParams[i] == null) { continue; }
                        cmd.Parameters.Add(sqlParams[i]);
                    }
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        #endregion
        #region 执行事务
        /// <summary>
        /// 传入多个委托方法，需要每个方法不为空并且返回true，才会提交数据修改。否则回滚
        /// </summary>
        /// <param name="handlers"></param>
        /// <returns></returns>
        public bool ExecuteAsTransaction(params Func<IDbCommand, bool>[] handlers)
        {
            if (!ValidateHelper.IsPlumpArray(handlers))
            {
                return false;
            }
            ++this.QueryCount;
            MySqlTransaction trans = null;
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return false; }
                trans = this.con.BeginTransaction();
                cmd.Transaction = trans;
                //事务初始化
                for (int i = 0; i < handlers.Length; ++i)
                {
                    if (handlers[i] == null || !handlers[i].Invoke(cmd))
                    {
                        //有操作执行失败，回滚
                        trans.Rollback();
                        return false;
                    }
                }
                //事务提交
                trans.Commit();
                return true;
            }
            catch (Exception e)
            {
                //事务回滚
                if (trans != null) { trans.Rollback(); }
                return false;
            }
            finally
            {
                DBHelper.Release(trans);
                DBHelper.Release(null, cmd, null);
            }
        }
        /// <summary>
        /// 事务执行，要么都成功，要么回滚到初始状态
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExecuteAsTransaction(Dictionary<string, Dictionary<string, object>> sqlAndData)
        {
            Func<IDbCommand, bool>[] handlers = new Func<IDbCommand, bool>[sqlAndData.Keys.Count];
            for (int i = 0; i < handlers.Length; ++i)
            {
                handlers[i] = delegate(IDbCommand cmd)
                {
                    if (cmd == null) { return false; }
                    MySqlCommand mycmd = null;
                    if ((mycmd = cmd as MySqlCommand) == null) { return false; }
                    mycmd.CommandText = sqlAndData.Keys.ToList()[i];
                    Dictionary<string, object> data = sqlAndData[mycmd.CommandText];
                    data.Keys.ToList().ForEach(delegate(string key)
                    {
                        mycmd.Parameters.AddWithValue(key, data[key]);
                    });
                    return mycmd.ExecuteNonQuery() > 0;
                };
            }
            return ExecuteAsTransaction(handlers);
        }
        #endregion
        #region 获取reader
        /// <summary>
        /// 用reader读取
        /// </summary>
        /// <param name="sql">sql语言</param>
        /// <returns>reader</returns>
        public IDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }

        public IDataReader ExecuteReader(string sql, Dictionary<string, object> sqlParams)
        {
            MySqlParameter[] param = null;
            if (sqlParams != null)
            {
                param = new MySqlParameter[sqlParams.Count];
                int i = 0;
                sqlParams.Keys.ToList().ForEach(delegate(string key)
                {
                    param[i] = new MySqlParameter(key, sqlParams[key]);
                    ++i;
                });
            }
            return ExecuteReaderWithParams(sql, param);
        }
        public IDataReader ExecuteReaderWithParams(string sql, params DbParameter[] param)
        {
            ++this.QueryCount;
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return null; }
                cmd.CommandText = sql;
                if (param != null && param.Count() > 0)
                {
                    param.ToList().ForEach(delegate(DbParameter p)
                    {
                        if (p != null)
                        {
                            if (p is MySqlParameter)
                            {
                                cmd.Parameters.Add(p as MySqlParameter);
                            }
                            else
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                    });
                }
                //cmd.ExecuteReader(CommandBehavior.CloseConnection)
                //用这种方法可以在reader关闭的时候关闭其依赖的连接对象
                //但是我们这里con是不手动关闭的，所以不用关闭。
                return cmd.ExecuteReader();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        #endregion
        #region 获取第一行第一个
        /// <summary>
        /// 返回第一条记录
        /// </summary>
        /// <param name="sql">sql语言</param>
        /// <returns>string</returns>
        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        public object ExecuteScalar(string sql, Dictionary<string, object> sqlParams)
        {
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return null; }
                cmd.CommandText = sql;
                if (sqlParams != null)
                {
                    foreach (KeyValuePair<string, object> entry in sqlParams)
                    {
                        cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                    }
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        #endregion
        #region 获取dataset
        /// <summary>
        /// 返回dataset
        /// </summary>
        /// <param name="sql">sql语言</param>
        /// <param name="cmdType">command类型（insert,delete,select,update）</param>
        /// <param name="datatable">dataset中table的名字</param>
        /// <returns>dataset</returns>
        public DataSet ExecuteDataset(string sql)
        {
            return ExecuteDataset(sql, null);
        }

        public DataSet ExecuteDataset(string sql, Dictionary<string, object> sqlParams)
        {
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return null; }
                cmd.CommandText = sql;
                if (sqlParams != null)
                {
                    foreach (KeyValuePair<string, object> entry in sqlParams)
                    {
                        cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                    }
                }
                MySqlDataAdapter mda = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                mda.Fill(ds);
                mda.Dispose();
                return ds;
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        #endregion
        #region 获取datatable
        public DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null);
        }

        public DataTable ExecuteDataTable(string sql, Dictionary<string, object> sqlParams)
        {
            DataSet ds = ExecuteDataset(sql, sqlParams);
            return (ds != null && ds.Tables.Count > 0) ? ds.Tables[0] : null;
        }
        #endregion
        #region 存储过程
        /// <summary>
        /// 这个方法不要用
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcName"></param>
        /// <param name="param"></param>
        /// <param name="rowsAffected"></param>
        /// <param name="getNewCon"></param>
        /// <returns></returns>
        public T RunProcedure<T>(string storedProcName, IDataParameter[] param, out int rowsAffected)
        {
            rowsAffected = 0;
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return default(T); }
                cmd.CommandText = storedProcName;
                if (param != null && param.Count() > 0)
                {
                    foreach (SqlParameter p in param)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
                cmd.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
                rowsAffected = cmd.ExecuteNonQuery();
                return (T)cmd.Parameters["ReturnValue"].Value;
            }
            catch (Exception e)
            {
                return default(T);
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        #endregion
        #region 方法
        /// <summary>
        /// 存储文件
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="bs"></param>
        /// <param name="getNewCon"></param>
        /// <param name="autodispose"></param>
        /// <returns></returns>
        public int ExecuteFileToDb(string sql, Dictionary<string, byte[]> param)
        {
            MySqlCommand cmd = null;
            try
            {
                if ((cmd = this.InitCommand() as MySqlCommand) == null) { return 0; }
                cmd.CommandText = sql;
                if (param == null || param.Count == 0) { return 0; }
                List<string> keys = param.Keys.ToList<string>();
                for (int i = 0; i < param.Count; ++i)
                {
                    cmd.Parameters.AddWithValue(keys[i], param[keys[i]]);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                DBHelper.Release(null, cmd, null);
            }
        }
        /// <summary>
        /// 读取文件
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="getNewCon"></param>
        /// <returns></returns>
        public byte[] ReadFileFromDb(string sql, Dictionary<string, object> param)
        {
            try
            {
                object obj = this.ExecuteScalar(sql, param);
                /*
                if (XQ.Framework.core.ValidateHelper.Is<byte[]>(obj))
                {
                    return (byte[])obj;
                }*/
                if (obj != null && obj is byte[])
                {
                    return (byte[])obj;
                }
                return null;
            }
            catch (Exception e)
            {
                return null;
            }
        }
        /// <summary>
        /// 映射到实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <returns></returns>
        public T FillEntity<T>(DataRow row) where T : class,new()
        {
            T obj = new T();
            PropertyInfo[] props = obj.GetType().GetProperties();
            PropertyInfo prop = null;
            for (int i = 0; i < props.Count(); ++i)
            {
                if ((prop = props[i]) == null) { continue; }
                if (prop.CanWrite && row.Table.Columns.Contains(prop.Name))
                {
                    if (row[prop.Name] != DBNull.Value)
                    {
                        prop.SetValue(obj, row[prop.Name], null);
                    }
                }
            }
            return obj;
        }
        #endregion
        #region 关闭连接
        /// <summary>
        /// 
        /// </summary>
        public void Release()
        {
            if (this.OnConnectionClosed != null)
            {
                this.OnConnectionClosed.Invoke();
            }
            DBHelper.Release(this.con, null, null);
        }
        /// <summary>
        /// IDispose接口的方法
        /// </summary>
        public void Dispose()
        {
            this.Release();
        }
        #endregion
    }

}